﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Text.RegularExpressions;

using System.IO;
using System.Text;

using System.Net;
using System.Net.Sockets;
using System.Threading;
using System.Net.Mail;

namespace ukeasbjWeb
{	
    /// <summary>
    /// ukeas_bonus “3合1奖金表”的摘要说明。
    /// 生成月奖金表，分为10个部分（奖1---奖10）生成数据， 然后计算本月奖金。
    /// </summary>
    public partial class ukeas_bonus : System.Web.UI.Page
    {
        //从配置文件Web.Config 中读出数据库设置
        string strCnn = ConfigurationManager.AppSettings["glCnnStr"]; 
        
        protected void Page_Load(object sender, EventArgs e)
        {
            // 在此处放置用户代码以初始化页面
            Common_SXD.Is_LongIn(Response, Session, Request);

            //////只允许顾问、文案、OM、总部会计、Admin 访问
            bool bIs_Counsellor = Convert.ToBoolean(Session["Is_counsellor"]);
            bool bIs_Writer = Convert.ToBoolean(Session["Is_Writer"]);
            bool bIs_O_M = Convert.ToBoolean(Session["Is_O_M"]);
            bool bIs_Admin = Convert.ToBoolean(Session["Is_Admin"]);
            bool bIs_Accountant = Convert.ToBoolean(Session["Is_Accountant"]);

            if (!bIs_Counsellor && !bIs_Writer && !bIs_O_M && !bIs_Admin && !bIs_Accountant)
            {
                Response.Write("<h2 align='center'><font face='宋体' color='red'>");
                Response.Write("<br>对不起，您不能使用本功能。" +
                    "<br>请用其它身份登录。");
                Response.Write("</fong></h2>");
                Response.End();

            }

            if (!IsPostBack)
            {	// 初始化页面
                Bt_Bonus_Exel.Enabled = false;

                //////顾问、文案，设定姓名，关闭选项
                DDList_Counsellor_Writer.Enabled = true;
                DDList_Counsellor_Writer.Visible = true;
                if ((bIs_Counsellor || bIs_Writer))
                {
                    if (!bIs_O_M)
                        if (!bIs_Admin)
                            if (!bIs_Accountant)
                            {
                                DDList_Counsellor_Writer.Enabled = false;
                                DDList_Counsellor_Writer.Visible = false;
                            }
                            
                }
                
                Init_Office_YearMonth();
                Init_CounsellorWriter();

            }

        }

        decimal J1, J2, J3, J4, J5, J6, J7, J8, J9, J10, JJ1;

        /// <summary>
        ///  初始化：计算奖金表，考虑'北京','上海','其他8个办公室'三种计算公式
        /// </summary>
        private void Init_Total_Bonus(string sTime)
        {
            ////////////////////////初始值为空
            Label_b1.Text = "";

            Label_b3.Text = "";
            Label_b4.Text = "";

            Label_b5.Text = "";
            Label_b6.Text = "";

            Label_b7.Text = "";
            Label_b8.Text = "";

            Label_b9.Text = "";
            Label_b10.Text = "";

            Label_Total.Text = "";
            ///////////////////////			

            System.DateTime startDateTime20090925 =
                new System.DateTime(2009,        // Year
                9,            // Month
                25,            // Day
                0,            // Hour
                0,            // Minute
                0,            // Second
                0             // Millisecond
                );

            System.DateTime flagStartDateTime = Convert.ToDateTime(Common_SXD.strStartDate);

            JJ1 = J1 - J2;
            decimal b1 = 0, b5 = 0, b6 = 0, b9 = 0, b10 = 0;
            string strOffice = Request["Office"].ToString().Trim();

            if (strOffice == "北京" && flagStartDateTime <= startDateTime20090925)
            {
                //////////////北京（上海）有30000元指标的奖1
                if (JJ1 < 0)
                    b1 = Math.Round(JJ1 * 0.05m * 0.333m, 2);
                else if (JJ1 >= 0 && JJ1 < 30000)
                    b1 = 0;
                else if (JJ1 >= 30000 && JJ1 < 50000)
                    b1 = Math.Round(JJ1 * 0.05m * 0.333m, 2);
                else if (JJ1 >= 50000)
                    b1 = Math.Round(JJ1 * 0.05m * 0.333m + JJ1 * 0.01m, 2);

                ///////////////北京奖9
                b9 = Math.Round(J9 * 0.05m * 0.2085m, 2);
                ///////////////北京奖10
                b10 = Math.Round(J10 * 0.05m * 0.2085m, 2);

            }
            else if (strOffice == "上海" || (strOffice == "北京" && flagStartDateTime > startDateTime20090925))
            {
                /////////////北京（上海）有30000元指标的奖1
                if (JJ1 < 0)
                    b1 = JJ1 * 0.05m * 0.333m;
                else if (JJ1 >= 0 && JJ1 < 30000)
                    b1 = 0;
                else if (JJ1 >= 30000 && JJ1 < 50000)
                    b1 = JJ1 * 0.05m * 0.333m;
                else if (JJ1 >= 50000)
                    b1 = JJ1 * 0.05m * 0.333m + JJ1 * 0.01m;
                b1 = Math.Round(b1, 2);

                ///////////////上海奖9
                b9 = Math.Round(J9 * 0.05m * 0.167m, 2);
                //////////////上海奖10
                b10 = Math.Round(J10 * 0.05m * 0.25m, 2);

            }
            else
            {
                ///////无30000元指标的奖1
                ///
                if (JJ1 >= 50000)
                    b1 = JJ1 * 0.05m * 0.333m + JJ1 * 0.01m;
                else
                    b1 = JJ1 * 0.05m * 0.333m;
                b1 = Math.Round(b1, 2);

                ///////上海奖9
                b9 = Math.Round(J9 * 0.05m * 0.167m, 2);
                //////上海奖10
                b10 = Math.Round(J10 * 0.05m * 0.25m, 2);
            }

            ///////奖3
            b5 = Math.Round(J5 * 0.05m * 0.25m, 2);
            ///////奖3
            b6 = Math.Round(J6 * 0.05m * 0.25m, 2);

            Label_b1.Text = b1.ToString().Trim();

            Label_b3.Text = J3.ToString().Trim();
            Label_b4.Text = J4.ToString().Trim();

            Label_b5.Text = b5.ToString().Trim();
            Label_b6.Text = b6.ToString().Trim();

            Label_b7.Text = J7.ToString().Trim();
            Label_b8.Text = J8.ToString().Trim();

            Label_b9.Text = b9.ToString().Trim();
            Label_b10.Text = b10.ToString().Trim();

            decimal total = b1 + J3 + b5 + J7 + b9 + b10 - J4 - b6 - J8;
            total = Math.Round(total, 2);
            Label_Total.Text = total.ToString().Trim();

            ////////////////当月签约
            {
                HtmlTableRow row = new HtmlTableRow();

                HtmlTableCell cell = new HtmlTableCell();
                cell.Controls.Add(new LiteralControl(" "));
                row.Cells.Add(cell);

                HtmlTableCell cell2 = new HtmlTableCell();
                cell2.Controls.Add(new LiteralControl("JJ1"));
                row.Cells.Add(cell2);

                HtmlTableCell cell3 = new HtmlTableCell();
                cell3.Controls.Add(new LiteralControl(JJ1.ToString().Trim()));
                row.Cells.Add(cell3);

                Table_Bonus_1.Rows.Add(row);

            }
            ///////////////////////////////

        }

        /// <summary>
        ///  初始化奖金1，顾问收费生签约奖
        /// </summary>
        /// <remarks>
        /// <newpara>
        /// A) 只有一期款发生在本月， 二期款发生在其它月份 
        /// </newpara>
        /// <newpara> 
        /// B) 只有二期款发生在本月， 一期款发生在其他月份 
        /// </newpara>
        /// <newpara> 
        /// C) 二期款、 一期款均发生在本月
        /// </newpara>
        /// </remarks>
        private void Init_Tables_Bonus_1()
        {

            //清除表中的内容
            Table_Bonus_1.Rows.Clear();
            //			Table_Bonus_2.Rows.Clear();
            J1 = 0;

            SqlConnection sqlCnn = new SqlConnection();
            sqlCnn.ConnectionString = strCnn;
            sqlCnn.Open(); // 建立连接
            SqlCommand sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象

            /// <newpara> 
            /// 读取地区、顾问（文案）字段
            /// </newpara>
            string strOffice = Request["Office"].ToString().Trim();
            string strCounsellor_Writer;
            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            string sSql = "";
            sSql = "select Student_Name, Student_Type, First_Payment as 'Shishoujinge'  from contract where ";

            string strFilter = "";
            /// <newpara> 
            /// A) 只有一期款发生在本月， 二期款发生在其它月份
            /// </newpara>
            strFilter += "Office like '" + strOffice + "' AND";
            strFilter += "((Date_first_payment >= '" + Common_SXD.strStartDate + "') AND (Date_first_payment <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND ((Date_second_payment < '" + Common_SXD.strStartDate + "') OR (Date_second_payment > '" + Common_SXD.strEndingDate;
            strFilter += "'))   AND ( Student_Type = '全程一次' OR  Student_Type = '英美联办' OR  Student_Type = '英澳联办' OR  Student_Type = '英加联办' OR  Student_Type = '全程二次' OR  Student_Type = '申请学校' OR  Student_Type = '签证')  AND counsellor = '" + strCounsellor_Writer + "'";

            string sOrder = "";
            sOrder = " order by " + "Series_No DESC";


            sqlCmd.CommandText = sSql + strFilter + sOrder;

            SqlDataReader sqlReader;
            try
            {
                sqlReader = sqlCmd.ExecuteReader();
            }
            catch (Exception err)
            {
                Response.Write("<li>打开数据库出错：" + err.ToString());
                sqlCnn.Close(); // 关闭数据库连接
                return;
            }

            int nCol = sqlReader.FieldCount;

            //首先生成标题行
            {
                HtmlTableRow row = new HtmlTableRow();
                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    string temp = Common_SXD.English_Chinese(sqlReader.GetName(i));
                    cell.Controls.Add(new LiteralControl(temp.Trim()));
                    row.Cells.Add(cell);
                }

                Table_Bonus_1.Rows.Add(row);
                row.BgColor = "Tan";
            }

            // 依次读出数据
            while (sqlReader.Read())
            {
                HtmlTableRow row = new HtmlTableRow();
                String sNo_Series = sqlReader.GetString(0);

                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
                    row.Cells.Add(cell);
                    if (i == nCol - 1)
                        J1 += Convert.ToDecimal(sqlReader.GetValue(i).ToString());

                }

                Table_Bonus_1.Rows.Add(row);
            }
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接
            ///////////////////////////////////////////////////

            sqlCnn.Open(); // 建立连接
            sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象

            sSql = "select Student_Name, Student_Type, Second_Payment   from contract where ";

            /// <newpara> 
            /// B) 二期款发生在本月， 一期款发生在其他月份 
            /// </newpara>
            strFilter = "";
            strFilter += "Office like '" + strOffice + "' AND";
            strFilter += "((Date_Second_payment >= '" + Common_SXD.strStartDate + "') AND (Date_Second_payment <= '" + Common_SXD.strEndingDate + "')) AND ((Date_First_payment < '" + Common_SXD.strStartDate + "') OR (Date_First_payment > '" + Common_SXD.strEndingDate + "'))   AND ( Student_Type = '全程一次' OR  Student_Type = '英美联办' OR  Student_Type = '英澳联办' OR  Student_Type = '英加联办' OR  Student_Type = '全程二次' OR  Student_Type = '申请学校' OR  Student_Type = '签证')  AND counsellor = '" + strCounsellor_Writer + "'";
            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);		

            sqlCmd.CommandText = sSql + strFilter;

            //			SqlDataReader sqlReader;
            try
            {
                sqlReader = sqlCmd.ExecuteReader();
            }
            catch (Exception err)
            {
                Response.Write("<li>打开数据库出错：" + err.ToString());
                sqlCnn.Close(); // 关闭数据库连接
                return;
            }

            // 读入第一个查询结果集， 即 Contract 中的数据
            nCol = sqlReader.FieldCount;

            // 依次读出数据
            while (sqlReader.Read())
            {
                HtmlTableRow row = new HtmlTableRow();
                String sNo_Series = sqlReader.GetString(0);

                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
                    row.Cells.Add(cell);
                    if (i == nCol - 1)
                        J1 += Convert.ToDecimal(sqlReader.GetValue(i).ToString());
                }

                Table_Bonus_1.Rows.Add(row);
            }
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接

            sqlCnn.Open(); // 建立连接
            sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象

            sSql = "select Student_Name, Student_Type, First_Payment + Second_Payment   from contract where ";

            /// <newpara> 
            /// C) 二期款、 一期款均发生在本月
            /// </newpara>
            strFilter = "";
            strFilter += "Office like '" + strOffice + "' AND";
            strFilter += "((Date_Second_payment >= '" + Common_SXD.strStartDate + "') AND (Date_Second_payment <= '" + Common_SXD.strEndingDate + "')) AND ((Date_First_payment >= '" + Common_SXD.strStartDate + "') AND (Date_First_payment <= '" + Common_SXD.strEndingDate + "'))   AND ( Student_Type = '全程一次' OR  Student_Type = '英美联办' OR  Student_Type = '英澳联办' OR  Student_Type = '英加联办' OR  Student_Type = '全程二次' OR  Student_Type = '申请学校' OR  Student_Type = '签证')  AND counsellor = '" + strCounsellor_Writer + "'";
            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);

            sqlCmd.CommandText = sSql + strFilter;

            //			SqlDataReader sqlReader;
            try
            {
                sqlReader = sqlCmd.ExecuteReader();
            }
            catch (Exception err)
            {
                Response.Write("<li>打开数据库出错：" + err.ToString());
                sqlCnn.Close(); // 关闭数据库连接
                return;
            }

            nCol = sqlReader.FieldCount;

            // 依次读出数据
            while (sqlReader.Read())
            {
                HtmlTableRow row = new HtmlTableRow();
                String sNo_Series = sqlReader.GetString(0);

                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
                    row.Cells.Add(cell);
                    if (i == nCol - 1)
                        J1 += Convert.ToDecimal(sqlReader.GetValue(i).ToString());
                }

                Table_Bonus_1.Rows.Add(row);
            }
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接

            ///<newpara>
            /// 生成奖1的小计	
            ///</newpara>
            {
                HtmlTableRow row = new HtmlTableRow();

                HtmlTableCell cell = new HtmlTableCell();
                cell.Controls.Add(new LiteralControl(" "));
                row.Cells.Add(cell);

                HtmlTableCell cell2 = new HtmlTableCell();
                cell2.Controls.Add(new LiteralControl("合计:"));
                row.Cells.Add(cell2);

                HtmlTableCell cell3 = new HtmlTableCell();
                cell3.Controls.Add(new LiteralControl(J1.ToString().Trim()));
                row.Cells.Add(cell3);

                Table_Bonus_1.Rows.Add(row);
                //			row.BgColor="Tan";
            }
            ///////////////////////////////

        }

        /// <summary>
        ///  初始化奖金3，顾问零中介签约奖。
        /// </summary>
        /// <remarks>
        /// <newpara>
        /// A) 本月，只有第一次付款发生在本月， First_Payment大于等于1000
        /// </newpara>
        /// <newpara>
        /// B) 本月，发生第二次付款， 第一次付款小于1000, 2次款之和大于等于1000。
        /// </newpara>
        /// </remarks>
        private void Init_Tables_Bonus_3()
        {

            //清除表中的内容
            Table_Bonus_3.Rows.Clear();

            J3 = 0;

            SqlConnection sqlCnn = new SqlConnection();
            sqlCnn.ConnectionString = strCnn;
            sqlCnn.Open(); // 建立连接
            SqlCommand sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象

            string strOffice = Request["Office"].ToString().Trim();
            string strCounsellor_Writer;
            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            string sSql = "";

            sSql = "select Student_Name, First_Payment as 'Yajing', 50 as 'Bonus'  from contract where ";

            string strFilter = "";
            /// <newpara>
            /// A) 本月，只有第一次付款发生在本月， First_Payment >=1000
            /// </newpara>
            strFilter += "Office like '" + strOffice + "' AND";
            strFilter += "((Date_first_payment >= '" + Common_SXD.strStartDate + "') AND (Date_first_payment <= '" + Common_SXD.strEndingDate + "')) AND ((Date_second_payment < '" + Common_SXD.strStartDate + "') OR (Date_second_payment > '" + Common_SXD.strEndingDate + "'))  AND First_Payment >= 1000 AND Student_Type = '零中介'  AND counsellor = '" + strCounsellor_Writer + "'";

            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);

            sqlCmd.CommandText = sSql + strFilter;

            SqlDataReader sqlReader;
            try
            {
                sqlReader = sqlCmd.ExecuteReader();
            }
            catch (Exception err)
            {
                Response.Write("<li>打开数据库出错：" + err.ToString());
                sqlCnn.Close(); // 关闭数据库连接
                return;
            }

            int nCol = sqlReader.FieldCount;

            //首先生成标题行
            {
                HtmlTableRow row = new HtmlTableRow();
                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    string temp = Common_SXD.English_Chinese(sqlReader.GetName(i));
                    cell.Controls.Add(new LiteralControl(temp.Trim()));
                    row.Cells.Add(cell);
                }

                Table_Bonus_3.Rows.Add(row);
                row.BgColor = "Tan";
            }

            // 依次读出数据
            while (sqlReader.Read())
            {
                HtmlTableRow row = new HtmlTableRow();
                String sNo_Series = sqlReader.GetString(0);

                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
                    row.Cells.Add(cell);
                    if (i == nCol - 1)
                        J3 += Convert.ToDecimal(sqlReader.GetValue(i).ToString());
                }

                Table_Bonus_3.Rows.Add(row);
            }
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接
            /////////////////////////////////////////////////////////

            sqlCnn.Open(); // 建立连接
            sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象

            sSql = "select Student_Name, First_Payment + Second_Payment as 'Yajing', 50 as 'Bonus'  from contract where ";

            /// <newpara>
            /// B) 考虑第二次付款发生在本月， 第一次付款小于1000, 2次款之和大于等于1000。
            /// </newpara>
            strFilter = "";
            strFilter += "Office like '" + strOffice + "' AND";
            strFilter += "((Date_Second_payment >= '" + Common_SXD.strStartDate + "') AND (Date_Second_payment <= '" + Common_SXD.strEndingDate + "')) AND First_Payment < 1000 AND First_Payment + Second_Payment >= 1000   AND Student_Type = '零中介'  AND counsellor = '" + strCounsellor_Writer + "'";

            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);

            sqlCmd.CommandText = sSql + strFilter;

            try
            {
                sqlReader = sqlCmd.ExecuteReader();
            }
            catch (Exception err)
            {
                Response.Write("<li>打开数据库出错：" + err.ToString());
                sqlCnn.Close(); // 关闭数据库连接
                return;
            }


            nCol = sqlReader.FieldCount;

            // 依次读出数据
            while (sqlReader.Read())
            {
                HtmlTableRow row = new HtmlTableRow();
                String sNo_Series = sqlReader.GetString(0);

                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
                    row.Cells.Add(cell);
                    if (i == nCol - 1)
                        J3 += Convert.ToDecimal(sqlReader.GetValue(i).ToString());
                }

                Table_Bonus_3.Rows.Add(row);
            }
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接

            ////////////////生成小计
            {
                HtmlTableRow row = new HtmlTableRow();

                HtmlTableCell cell = new HtmlTableCell();
                cell.Controls.Add(new LiteralControl(" "));
                row.Cells.Add(cell);

                HtmlTableCell cell2 = new HtmlTableCell();
                cell2.Controls.Add(new LiteralControl("合计:"));
                row.Cells.Add(cell2);

                HtmlTableCell cell3 = new HtmlTableCell();
                cell3.Controls.Add(new LiteralControl(J3.ToString().Trim()));
                row.Cells.Add(cell3);

                Table_Bonus_3.Rows.Add(row);
                //			row.BgColor="Tan";
            }
            ///////////////////////////////

        }



        /// <summary>
        ///  初始化奖金5，文案收费生完成Offer/Visa奖
        /// </summary>
        /// <remarks>
        /// <newpara>
        /// A) '全程一次'， '英美联办'，'申请学校'，offer完成奖。
        /// </newpara>
        /// <newpara>
        /// B) '全程二次'的一期款， 文案offer完成奖。
        /// </newpara>
        /// <newpara>
        /// C) '全程二次'的二期款，visa完成奖。
        /// </newpara>
        /// <newpara>
        /// D) '签证'，visa完成奖。
        /// </newpara>
        /// </remarks>
        private void Init_Tables_Bonus_5()
        {

            //清除表中的内容
            Table_Bonus_5.Rows.Clear();
            //			Table_Bonus_2.Rows.Clear();
            J5 = 0;

            SqlConnection sqlCnn = new SqlConnection();
            sqlCnn.ConnectionString = strCnn;
            sqlCnn.Open(); // 建立连接
            SqlCommand sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象

            string strOffice = Request["Office"].ToString().Trim();
            string strCounsellor_Writer;
            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            string sSql = "";

            sSql = "select Student_Name, Student_Type, First_Payment + Second_Payment as 'Shishoujinge' from contract where ";

            string strFilter = "";
            /// <newpara>
            /// A) '全程一次'， '英美联办'，'申请学校'offer完成奖
            /// </newpara>
            //strFilter += "Office like '" + strOffice + "' AND";
            strFilter += "((Date_Completion_Offer >= '" + Common_SXD.strStartDate + "') AND (Date_Completion_Offer <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND ( Student_Type = '全程一次' OR  Student_Type = '英美联办' OR  Student_Type = '英澳联办' OR  Student_Type = '英加联办' OR Student_Type = '申请学校' )  AND Writer = '" + strCounsellor_Writer + "'";

            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);


            sqlCmd.CommandText = sSql + strFilter;

            SqlDataReader sqlReader;
            try
            {
                sqlReader = sqlCmd.ExecuteReader();
            }
            catch (Exception err)
            {
                Response.Write("<li>打开数据库出错：" + err.ToString());
                sqlCnn.Close(); // 关闭数据库连接
                return;
            }

            int nCol = sqlReader.FieldCount;

            //首先生成标题行
            {
                HtmlTableRow row = new HtmlTableRow();
                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    string temp = Common_SXD.English_Chinese(sqlReader.GetName(i));
                    cell.Controls.Add(new LiteralControl(temp.Trim()));
                    row.Cells.Add(cell);
                }

                Table_Bonus_5.Rows.Add(row);
                row.BgColor = "Tan";
            }

            // 依次读出数据
            while (sqlReader.Read())
            {
                HtmlTableRow row = new HtmlTableRow();
                String sNo_Series = sqlReader.GetString(0);

                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
                    row.Cells.Add(cell);
                    if (i == nCol - 1)
                        J5 += Convert.ToDecimal(sqlReader.GetValue(i).ToString());
                }

                Table_Bonus_5.Rows.Add(row);

            }
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接
            ///////////////////////////////////////////////////////


            sqlCnn = new SqlConnection();
            sqlCnn.ConnectionString = strCnn;
            sqlCnn.Open(); // 建立连接
            sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象

            strOffice = Request["Office"].ToString().Trim();

            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            sSql = "";

            sSql = "select Student_Name, Student_Type, First_Payment as 'Shishoujinge' from contract where ";

            strFilter = "";
            /// <newpara>
            /// B) '全程二次'的一期款， 文案offer完成奖。
            /// </newpara>
//            strFilter += "Office like '" + strOffice + "' AND";
            strFilter += "((Date_Completion_Offer >= '" + Common_SXD.strStartDate + "') AND (Date_Completion_Offer <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND  Student_Type = '全程二次'  AND Writer = '" + strCounsellor_Writer + "'";

            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);


            sqlCmd.CommandText = sSql + strFilter;

            try
            {
                sqlReader = sqlCmd.ExecuteReader();
            }
            catch (Exception err)
            {
                Response.Write("<li>打开数据库出错：" + err.ToString());
                sqlCnn.Close(); // 关闭数据库连接
                return;
            }

            nCol = sqlReader.FieldCount;

            // 依次读出数据
            while (sqlReader.Read())
            {
                HtmlTableRow row = new HtmlTableRow();
                String sNo_Series = sqlReader.GetString(0);

                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
                    row.Cells.Add(cell);
                    if (i == nCol - 1)
                        J5 += Convert.ToDecimal(sqlReader.GetValue(i).ToString());
                }

                Table_Bonus_5.Rows.Add(row);

            }
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接
            ///////////////////////////////////////////////////////////////////

            sqlCnn = new SqlConnection();
            sqlCnn.ConnectionString = strCnn;
            sqlCnn.Open(); // 建立连接
            sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象

            strOffice = Request["Office"].ToString().Trim();

            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            sSql = "";
            sSql = "select Student_Name, Student_Type, Second_Payment as 'Shishoujinge' from contract where ";

            strFilter = "";
            //strFilter += "Office like '" + strOffice + "' AND";
            /// <newpara>
            /// C) '全程二次'的第2次付费, visa完成
            /// </newpara>
            strFilter += "((Date_Completion_Contract >= '" + Common_SXD.strStartDate + "') AND (Date_Completion_Contract <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND  Student_Type = '全程二次'  AND Writer = '" + strCounsellor_Writer + "'";

            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);


            sqlCmd.CommandText = sSql + strFilter;

            try
            {
                sqlReader = sqlCmd.ExecuteReader();
            }
            catch (Exception err)
            {
                Response.Write("<li>打开数据库出错：" + err.ToString());
                sqlCnn.Close(); // 关闭数据库连接
                return;
            }


            // 读入第一个查询结果集， 即 Contract 中的数据
            nCol = sqlReader.FieldCount;

            // 依次读出数据
            while (sqlReader.Read())
            {
                HtmlTableRow row = new HtmlTableRow();
                String sNo_Series = sqlReader.GetString(0);

                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
                    row.Cells.Add(cell);
                    if (i == nCol - 1)
                        J5 += Convert.ToDecimal(sqlReader.GetValue(i).ToString());
                }

                Table_Bonus_5.Rows.Add(row);

            }
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接

            ///////////////////////////////////////////////////////
            sqlCnn = new SqlConnection();
            sqlCnn.ConnectionString = strCnn;
            sqlCnn.Open(); // 建立连接
            sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象

            strOffice = Request["Office"].ToString().Trim();

            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            sSql = "";
            sSql = "select Student_Name, Student_Type, First_Payment + Second_Payment - Termination_Refund as 'Shishoujinge' from contract where ";

            strFilter = "";
            /// <newpara>
            /// D) '签证'，visa完成。
            /// </newpara>
            //strFilter += "Office like '" + strOffice + "' AND";
            strFilter += "((Date_Completion_Contract >= '" + Common_SXD.strStartDate + "') AND (Date_Completion_Contract <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND ( Student_Type = '签证')  AND Writer = '" + strCounsellor_Writer + "'";

            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);

            sqlCmd.CommandText = sSql + strFilter;

            try
            {
                sqlReader = sqlCmd.ExecuteReader();
            }
            catch (Exception err)
            {
                Response.Write("<li>打开数据库出错：" + err.ToString());
                sqlCnn.Close(); // 关闭数据库连接
                return;
            }

            nCol = sqlReader.FieldCount;

            // 依次读出数据
            while (sqlReader.Read())
            {
                HtmlTableRow row = new HtmlTableRow();
                String sNo_Series = sqlReader.GetString(0);

                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
                    row.Cells.Add(cell);
                    if (i == nCol - 1)
                        J5 += Convert.ToDecimal(sqlReader.GetValue(i).ToString());
                }

                Table_Bonus_5.Rows.Add(row);
            }
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接

            ////////////////生成小计
            {
                HtmlTableRow row = new HtmlTableRow();

                HtmlTableCell cell = new HtmlTableCell();
                cell.Controls.Add(new LiteralControl(" "));
                row.Cells.Add(cell);

                HtmlTableCell cell2 = new HtmlTableCell();
                cell2.Controls.Add(new LiteralControl("合计:"));
                row.Cells.Add(cell2);

                HtmlTableCell cell3 = new HtmlTableCell();
                cell3.Controls.Add(new LiteralControl(J5.ToString().Trim()));
                row.Cells.Add(cell3);

                Table_Bonus_5.Rows.Add(row);
                //			row.BgColor="Tan";
            }
            ///////////////////////////////

        }

        /// <summary>
        ///  初始化奖金2，顾问收费生退约
        /// </summary>
        /// <remarks>
        /// <newpara>
        /// A) '全程一次', '英美联办'，'全程二次', '申请学校', '签证'的实际退费金额。对于有3000元指标的办公室，不考虑是否领过奖。
        /// </newpara>
        /// </remarks>
        private void Init_Tables_Bonus_2()
        {

            //清除表中的内容
            Table_Bonus_2.Rows.Clear();
            //			Table_Bonus_2.Rows.Clear();
            J2 = 0;

            SqlConnection sqlCnn = new SqlConnection();
            sqlCnn.ConnectionString = strCnn;
            sqlCnn.Open(); // 建立连接
            SqlCommand sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象


            string strOffice = Request["Office"].ToString().Trim();
            string strCounsellor_Writer;
            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            string sSql = "";

            sSql = "select Student_Name, Student_Type, Termination_Refund as 'Shituijinge' from contract where ";

            string strFilter = "";
            /// <newpara>
            /// A) '全程一次', '英美联办'，'全程二次', '申请学校', '签证'的实际退费金额。对于有3000元指标的办公室，不考虑是否领过奖。
            /// </newpara>
            strFilter += "Office like '" + strOffice + "' AND";
            strFilter += "((Date_Termination_Refund >= '" + Common_SXD.strStartDate + "') AND (Date_Termination_Refund <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND ( Student_Type = '全程一次' OR  Student_Type = '全程二次' OR  Student_Type = '申请学校' OR  Student_Type = '签证')  AND Counsellor = '";
            strFilter += strCounsellor_Writer + "'AND Termination_Refund > 0 ";

            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);

            sqlCmd.CommandText = sSql + strFilter;

            SqlDataReader sqlReader;
            try
            {
                sqlReader = sqlCmd.ExecuteReader();
            }
            catch (Exception err)
            {
                Response.Write("<li>打开数据库出错：" + err.ToString());
                sqlCnn.Close(); // 关闭数据库连接
                return;
            }


            // 读入第一个查询结果集， 即 Contract 中的数据
            int nCol = sqlReader.FieldCount;

            //首先生成标题行
            {
                HtmlTableRow row = new HtmlTableRow();
                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    string temp = Common_SXD.English_Chinese(sqlReader.GetName(i));
                    cell.Controls.Add(new LiteralControl(temp.Trim()));
                    row.Cells.Add(cell);
                }

                Table_Bonus_2.Rows.Add(row);
                row.BgColor = "Tan";
            }

            // 依次读出数据
            while (sqlReader.Read())
            {
                HtmlTableRow row = new HtmlTableRow();
                String sNo_Series = sqlReader.GetString(0);

                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
                    row.Cells.Add(cell);
                    if (i == nCol - 1)
                        J2 += Convert.ToDecimal(sqlReader.GetValue(i).ToString());
                }

                Table_Bonus_2.Rows.Add(row);
            }
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接

            ////////////////生成小计
            {
                HtmlTableRow row = new HtmlTableRow();

                HtmlTableCell cell = new HtmlTableCell();
                cell.Controls.Add(new LiteralControl(" "));
                row.Cells.Add(cell);

                HtmlTableCell cell2 = new HtmlTableCell();
                cell2.Controls.Add(new LiteralControl("合计:"));
                row.Cells.Add(cell2);

                HtmlTableCell cell3 = new HtmlTableCell();
                cell3.Controls.Add(new LiteralControl(J2.ToString().Trim()));
                row.Cells.Add(cell3);

                Table_Bonus_2.Rows.Add(row);
                //			row.BgColor="Tan";
            }
            ///////////////////////////////

        }

        /// <summary>
        ///  初始化奖金6, 文案收费生Offer/Visa退约
        /// </summary>
        /// <remarks>
        /// <newpara>
        /// A) '全程一次'， '英美联办'，'申请学校'，offer退约。
        /// </newpara>
        /// <newpara>
        /// B) '全程二次'的 offer 完成退约（第二期合同没有执行）。
        /// </newpara>
        /// <newpara>
        /// </remarks>
        private void Init_Tables_Bonus_6()
        {

            //清除表中的内容
            Table_Bonus_6.Rows.Clear();
            //			Table_Bonus_2.Rows.Clear();
            J6 = 0;
            SqlConnection sqlCnn = new SqlConnection();
            sqlCnn.ConnectionString = strCnn;
            sqlCnn.Open(); // 建立连接
            SqlCommand sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象


            string strOffice = Request["Office"].ToString().Trim();
            string strCounsellor_Writer;
            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            string sSql = "";
            sSql = "select Student_Name, Student_Type, Termination_Refund as 'Shituijinge' from contract where ";

            string strFilter = "";
            /// <newpara>
            /// A) '全程一次'，'英美联办'，'申请学校'，offer退约。
            /// </newpara>
            //strFilter += "Office like '" + strOffice + "' AND";
            strFilter += "((Date_Termination_Refund >= '" + Common_SXD.strStartDate + "') AND (Date_Termination_Refund <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND Date_Completion_Offer > '1930-1-1' AND ( Student_Type = '全程一次' OR  Student_Type = '英美联办' OR  Student_Type = '英澳联办' OR  Student_Type = '英加联办' OR Student_Type = '申请学校' )  AND Writer = '";
            strFilter += strCounsellor_Writer + "'AND Termination_Refund > 0 ";

            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);


            sqlCmd.CommandText = sSql + strFilter;

            SqlDataReader sqlReader;
            try
            {
                sqlReader = sqlCmd.ExecuteReader();
            }
            catch (Exception err)
            {
                Response.Write("<li>打开数据库出错：" + err.ToString());
                sqlCnn.Close(); // 关闭数据库连接
                return;
            }


            // 读入第一个查询结果集， 即 Contract 中的数据
            int nCol = sqlReader.FieldCount;

            //首先生成标题行
            {
                HtmlTableRow row = new HtmlTableRow();
                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    string temp = Common_SXD.English_Chinese(sqlReader.GetName(i));
                    cell.Controls.Add(new LiteralControl(temp.Trim()));
                    row.Cells.Add(cell);
                }

                Table_Bonus_6.Rows.Add(row);
                row.BgColor = "Tan";
            }

            // 依次读出数据
            while (sqlReader.Read())
            {
                HtmlTableRow row = new HtmlTableRow();
                String sNo_Series = sqlReader.GetString(0);

                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
                    row.Cells.Add(cell);
                    if (i == nCol - 1)
                        J6 += Convert.ToDecimal(sqlReader.GetValue(i).ToString());
                }

                Table_Bonus_6.Rows.Add(row);

            }
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接
            ////////////////////////////////////////////////////////////////////////////////////////////////////////////////

            sqlCnn = new SqlConnection();
            sqlCnn.ConnectionString = strCnn;
            sqlCnn.Open(); // 建立连接
            sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象	

            sSql = "select Student_Name, Student_Type, Termination_Refund as 'Shituijinge' from contract where ";

            strFilter = "";
            /// <newpara>
            /// B) '全程二次'的 offer 完成退约（第二期合同没有执行）。
            /// </newpara>
            //strFilter += "Office like '" + strOffice + "' AND";
            strFilter += "((Date_Termination_Refund >= '" + Common_SXD.strStartDate + "') AND (Date_Termination_Refund <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND Second_Payment = 0 AND Date_Completion_Offer > '1930-1-1' AND ( Student_Type = '全程二次' )  AND Writer = '";
            strFilter += strCounsellor_Writer + "'AND Termination_Refund > 0 ";

            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);

            sqlCmd.CommandText = sSql + strFilter;

            try
            {
                sqlReader = sqlCmd.ExecuteReader();
            }
            catch (Exception err)
            {
                Response.Write("<li>打开数据库出错：" + err.ToString());
                sqlCnn.Close(); // 关闭数据库连接
                return;
            }


            // 读入第一个查询结果集， 即 Contract 中的数据
            nCol = sqlReader.FieldCount;

            // 依次读出数据
            while (sqlReader.Read())
            {
                HtmlTableRow row = new HtmlTableRow();
                String sNo_Series = sqlReader.GetString(0);

                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
                    row.Cells.Add(cell);
                }

                Table_Bonus_6.Rows.Add(row);

            }
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接

            ////////////////生成小计
            {
                HtmlTableRow row = new HtmlTableRow();

                HtmlTableCell cell = new HtmlTableCell();
                cell.Controls.Add(new LiteralControl(" "));
                row.Cells.Add(cell);

                HtmlTableCell cell2 = new HtmlTableCell();
                cell2.Controls.Add(new LiteralControl("合计:"));
                row.Cells.Add(cell2);

                HtmlTableCell cell3 = new HtmlTableCell();
                cell3.Controls.Add(new LiteralControl(J6.ToString().Trim()));
                row.Cells.Add(cell3);

                Table_Bonus_6.Rows.Add(row);
                //			row.BgColor="Tan";
            }
            ///////////////////////////////

        }

        /// <summary>
        ///  初始化奖金表9，顾问结案奖
        /// </summary>
        /// <remarks>
        /// <newpara>
        /// A) 顾问结案奖，4种合同，净收入（一期款 +  二期款 - 退款） 为结案的金额
        /// </newpara>
        /// </remarks>
        private void Init_Tables_Bonus_9()
        {

            //清除表中的内容
            Table_Bonus_9.Rows.Clear();
            //			Table_Bonus_2.Rows.Clear();
            J9 = 0;
            SqlConnection sqlCnn = new SqlConnection();
            sqlCnn.ConnectionString = strCnn;
            sqlCnn.Open(); // 建立连接
            SqlCommand sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象

            //生成 sql 命令串

            string strOffice = Request["Office"].ToString().Trim();
            string strCounsellor_Writer;
            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            string sSql = "";

            sSql = "select Student_Name, Student_Type, First_Payment + Second_Payment - Termination_Refund as 'Shishoujinge' from contract where ";

            string strFilter = "";
            /// <newpara>
            /// A) 顾问结案奖，4种合同，净收入（一期款 +  二期款 - 退款） 为结案的金额
            /// </newpara>
            strFilter += "Office like '" + strOffice + "' AND";
            strFilter += "((Date_Completion_Contract >= '" + Common_SXD.strStartDate + "') AND (Date_Completion_Contract <= '" + Common_SXD.strEndingDate + "')) AND ( Student_Type = '全程一次' OR  Student_Type = '英美联办' OR  Student_Type = '英澳联办' OR  Student_Type = '英加联办' OR  Student_Type = '全程二次' OR  Student_Type = '申请学校' OR  Student_Type = '签证')  AND Counsellor = '" + strCounsellor_Writer + "'";

            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);


            sqlCmd.CommandText = sSql + strFilter;

            SqlDataReader sqlReader;
            try
            {
                sqlReader = sqlCmd.ExecuteReader();
            }
            catch (Exception err)
            {
                Response.Write("<li>打开数据库出错：" + err.ToString());
                sqlCnn.Close(); // 关闭数据库连接
                return;
            }


            // 读入第一个查询结果集， 即 Contract 中的数据
            int nCol = sqlReader.FieldCount;

            //首先生成标题行
            {
                HtmlTableRow row = new HtmlTableRow();
                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    string temp = Common_SXD.English_Chinese(sqlReader.GetName(i));
                    cell.Controls.Add(new LiteralControl(temp.Trim()));
                    row.Cells.Add(cell);
                }

                Table_Bonus_9.Rows.Add(row);
                row.BgColor = "Tan";
            }

            // 依次读出数据
            //			int j=0; 
            while (sqlReader.Read())
            {
                HtmlTableRow row = new HtmlTableRow();
                String sNo_Series = sqlReader.GetString(0);
                // Provide a different background color for alternating rows.
                //				if (j%2 == 1)
                //					row.BgColor="Gainsboro";


                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
                    row.Cells.Add(cell);
                    if (i == nCol - 1)
                        J9 += Convert.ToDecimal(sqlReader.GetValue(i).ToString());
                }

                Table_Bonus_9.Rows.Add(row);
                //				j++;
            }
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接

            ////////////////生成小计
            {
                HtmlTableRow row = new HtmlTableRow();

                HtmlTableCell cell = new HtmlTableCell();
                cell.Controls.Add(new LiteralControl(" "));
                row.Cells.Add(cell);

                HtmlTableCell cell2 = new HtmlTableCell();
                cell2.Controls.Add(new LiteralControl("合计:"));
                row.Cells.Add(cell2);

                HtmlTableCell cell3 = new HtmlTableCell();
                cell3.Controls.Add(new LiteralControl(J9.ToString().Trim()));
                row.Cells.Add(cell3);

                Table_Bonus_9.Rows.Add(row);
                //			row.BgColor="Tan";
            }
            ///////////////////////////////

        }

        /// <summary>
        ///  初始化奖金10，文案结案奖 
        /// </summary>
        /// <remarks>
        /// <newpara>
        /// A) 文案结案奖，4种合同，净收入（一期款 +  二期款 - 退款） 为结案的金额
        /// </newpara>
        /// </remarks>
        private void Init_Tables_Bonus_10()
        {

            //清除表中的内容
            Table_Bonus_10.Rows.Clear();
            //			Table_Bonus_2.Rows.Clear();
            J10 = 0;

            SqlConnection sqlCnn = new SqlConnection();
            sqlCnn.ConnectionString = strCnn;
            sqlCnn.Open(); // 建立连接
            SqlCommand sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象

            //生成 sql 命令串

            string strOffice = Request["Office"].ToString().Trim();
            string strCounsellor_Writer;
            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            string sSql = "";

            sSql = "select Student_Name, Student_Type, First_Payment + Second_Payment - Termination_Refund as 'Shishoujinge' from contract where ";
            //sSql += "Office like '" + strOffice + "' AND";
            /// <newpara>
            /// A) 文案结案奖，4种合同，净收入（一期款 +  二期款 - 退款） 为结案的金额
            /// </newpara>
            sSql += "((Date_Completion_Contract >= '" + Common_SXD.strStartDate + "') AND (Date_Completion_Contract <= '" + Common_SXD.strEndingDate + "')) AND ( Student_Type = '全程一次' OR  Student_Type = '英美联办' OR  Student_Type = '英澳联办' OR  Student_Type = '英加联办' OR  Student_Type = '全程二次' OR  Student_Type = '申请学校' OR  Student_Type = '签证')  AND Writer = '" + strCounsellor_Writer + "'";

            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);


            sqlCmd.CommandText = sSql;

            SqlDataReader sqlReader;
            try
            {
                sqlReader = sqlCmd.ExecuteReader();
            }
            catch (Exception err)
            {
                Response.Write("<li>打开数据库出错：" + err.ToString());
                sqlCnn.Close(); // 关闭数据库连接
                return;
            }


            // 读入第一个查询结果集， 即 Contract 中的数据
            int nCol = sqlReader.FieldCount;

            //首先生成标题行
            {
                HtmlTableRow row = new HtmlTableRow();
                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    string temp = Common_SXD.English_Chinese(sqlReader.GetName(i));
                    cell.Controls.Add(new LiteralControl(temp.Trim()));
                    row.Cells.Add(cell);
                }

                Table_Bonus_10.Rows.Add(row);
                row.BgColor = "Tan";
            }

            // 依次读出数据
            while (sqlReader.Read())
            {
                HtmlTableRow row = new HtmlTableRow();
                String sNo_Series = sqlReader.GetString(0);

                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
                    row.Cells.Add(cell);
                    if (i == nCol - 1)
                        J10 += Convert.ToDecimal(sqlReader.GetValue(i).ToString());
                }

                Table_Bonus_10.Rows.Add(row);
            }
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接

            ////////////////生成小计
            {
                HtmlTableRow row = new HtmlTableRow();

                HtmlTableCell cell = new HtmlTableCell();
                cell.Controls.Add(new LiteralControl(" "));
                row.Cells.Add(cell);

                HtmlTableCell cell2 = new HtmlTableCell();
                cell2.Controls.Add(new LiteralControl("合计:"));
                row.Cells.Add(cell2);

                HtmlTableCell cell3 = new HtmlTableCell();
                cell3.Controls.Add(new LiteralControl(J10.ToString().Trim()));
                row.Cells.Add(cell3);

                Table_Bonus_10.Rows.Add(row);
                //			row.BgColor="Tan";
            }
            ///////////////////////////////

        }

        /// <summary>
        ///  初始化奖金4，顾问零中介退约
        /// </summary>
        /// <remarks>
        /// <newpara>
        /// A) 本月发生的零中介退款，需要扣奖的合同。
        /// </newpara>
        /// </remarks>
        private void Init_Tables_Bonus_4()
        {

            //清除表中的内容
            Table_Bonus_4.Rows.Clear();
            J4 = 0;

            SqlConnection sqlCnn = new SqlConnection();
            sqlCnn.ConnectionString = strCnn;
            sqlCnn.Open(); // 建立连接
            SqlCommand sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象

            string strOffice = Request["Office"].ToString().Trim();
            string strCounsellor_Writer;
            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            string sSql = "";

            sSql = "select Student_Name, First_Payment + Second_Payment as 'Yajing', 50  as 'Koukuan' from contract where ";

            string strFilter = "";
            /// <newpara>
            /// A) 本月发生的零中介退款，需要扣奖的合同。
            /// </newpara>
            strFilter += "Office like '" + strOffice + "' AND";
            strFilter += "( Date_Termination_Refund >= '" + Common_SXD.strStartDate + "' AND  Date_Termination_Refund <= '" + Common_SXD.strEndingDate;
            strFilter += "' ) AND Termination_Refund<>0 AND (Notes like '%deduct%' ) AND (First_Payment + Second_Payment) >= 1000  AND Student_Type = '零中介'  AND Counsellor = '" + strCounsellor_Writer + "'";

            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);

            sqlCmd.CommandText = sSql + strFilter;

            SqlDataReader sqlReader;
            try
            {
                sqlReader = sqlCmd.ExecuteReader();
            }
            catch (Exception err)
            {
                Response.Write("<li>打开数据库出错：" + err.ToString());
                sqlCnn.Close(); // 关闭数据库连接
                return;
            }


            // 读入第一个查询结果集， 即 Contract 中的数据
            int nCol = sqlReader.FieldCount;

            //首先生成标题行
            {
                HtmlTableRow row = new HtmlTableRow();
                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    string temp = Common_SXD.English_Chinese(sqlReader.GetName(i));
                    cell.Controls.Add(new LiteralControl(temp.Trim()));
                    row.Cells.Add(cell);
                }

                Table_Bonus_4.Rows.Add(row);
                row.BgColor = "Tan";
            }

            // 依次读出数据
            while (sqlReader.Read())
            {
                HtmlTableRow row = new HtmlTableRow();
                String sNo_Series = sqlReader.GetString(0);

                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
                    row.Cells.Add(cell);
                    if (i == nCol - 1)
                        J4 += Convert.ToDecimal(sqlReader.GetValue(i).ToString());
                }

                Table_Bonus_4.Rows.Add(row);
            }
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接

            ////////////////生成小计
            {
                HtmlTableRow row = new HtmlTableRow();

                HtmlTableCell cell = new HtmlTableCell();
                cell.Controls.Add(new LiteralControl(" "));
                row.Cells.Add(cell);

                HtmlTableCell cell2 = new HtmlTableCell();
                cell2.Controls.Add(new LiteralControl("合计:"));
                row.Cells.Add(cell2);

                HtmlTableCell cell3 = new HtmlTableCell();
                cell3.Controls.Add(new LiteralControl(J4.ToString().Trim()));
                row.Cells.Add(cell3);

                Table_Bonus_4.Rows.Add(row);
                //			row.BgColor="Tan";
            }
            ///////////////////////////////

        }

        /// <summary>
        ///  初始化奖金8，文案零中介Offer退约。
        /// </summary>
        /// <remarks>
        /// <newpara>
        /// A) 文案零中介Offer退约，需要扣奖的合同，已经领取奖金，First_Payment >=1000
        /// </newpara>
        /// </remarks>
        private void Init_Tables_Bonus_8()
        {

            //清除表中的内容
            Table_Bonus_8.Rows.Clear();
            J8 = 0;

            SqlConnection sqlCnn = new SqlConnection();
            sqlCnn.ConnectionString = strCnn;
            sqlCnn.Open(); // 建立连接
            SqlCommand sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象

            //生成 sql 命令串
            ////将奖1 的数据填入 表格：
            // 本月，只有第一次付款

            string strOffice = Request["Office"].ToString().Trim();
            string strCounsellor_Writer;
            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            string sSql = "";
            sSql = "select Student_Name, First_Payment + Second_Payment as 'Yajing', 50  as 'Koukuan' from contract where ";

            string strFilter = "";
            /// <newpara>
            /// A) 文案零中介Offer退约，需要扣奖的合同，已经领取奖金，First_Payment >=1000
            /// </newpara>
            //strFilter += "Office like '" + strOffice + "' AND";
            strFilter += "( Date_Termination_Refund >= '" + Common_SXD.strStartDate + "' AND  Date_Termination_Refund <= '" + Common_SXD.strEndingDate;
            strFilter += "' ) AND Termination_Refund > 0 AND Date_Completion_Offer > '1930-1-1' AND (Notes like '%deduct%' ) AND (First_Payment + Second_Payment) >= 1000  AND Student_Type = '零中介'  AND Writer = '" + strCounsellor_Writer + "'";

            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);

            sqlCmd.CommandText = sSql + strFilter;

            SqlDataReader sqlReader;
            try
            {
                sqlReader = sqlCmd.ExecuteReader();
            }
            catch (Exception err)
            {
                Response.Write("<li>打开数据库出错：" + err.ToString());
                sqlCnn.Close(); // 关闭数据库连接
                return;
            }


            // 读入第一个查询结果集， 即 Contract 中的数据
            int nCol = sqlReader.FieldCount;

            //首先生成标题行
            {
                HtmlTableRow row = new HtmlTableRow();
                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    string temp = Common_SXD.English_Chinese(sqlReader.GetName(i));
                    cell.Controls.Add(new LiteralControl(temp.Trim()));
                    row.Cells.Add(cell);
                }

                Table_Bonus_8.Rows.Add(row);
                row.BgColor = "Tan";
            }

            // 依次读出数据
            //			int j=0; 
            while (sqlReader.Read())
            {
                HtmlTableRow row = new HtmlTableRow();
                String sNo_Series = sqlReader.GetString(0);
                // Provide a different background color for alternating rows.
                //				if (j%2 == 1)
                //					row.BgColor="Gainsboro";


                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
                    row.Cells.Add(cell);
                    if (i == nCol - 1)
                        J8 += Convert.ToDecimal(sqlReader.GetValue(i).ToString());
                }

                Table_Bonus_8.Rows.Add(row);
                //				j++;
            }
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接

            ////////////////生成小计
            {
                HtmlTableRow row = new HtmlTableRow();

                HtmlTableCell cell = new HtmlTableCell();
                cell.Controls.Add(new LiteralControl(" "));
                row.Cells.Add(cell);

                HtmlTableCell cell2 = new HtmlTableCell();
                cell2.Controls.Add(new LiteralControl("合计:"));
                row.Cells.Add(cell2);

                HtmlTableCell cell3 = new HtmlTableCell();
                cell3.Controls.Add(new LiteralControl(J8.ToString().Trim()));
                row.Cells.Add(cell3);

                Table_Bonus_8.Rows.Add(row);
                //			row.BgColor="Tan";
            }
            ///////////////////////////////

        }

        /// <summary>
        ///  初始化奖金7，文案零中介完成Offer奖。
        /// </summary>
        /// <remarks>
        /// <newpara>
        /// A) 文案零中介完成Offer奖， 收到第一个offer，First_Payment >=1000
        /// </newpara>
        /// </remarks>
        private void Init_Tables_Bonus_7()
        {

            //清除表中的内容
            Table_Bonus_7.Rows.Clear();
            J7 = 0;

            SqlConnection sqlCnn = new SqlConnection();
            sqlCnn.ConnectionString = strCnn;
            sqlCnn.Open(); // 建立连接
            SqlCommand sqlCmd = sqlCnn.CreateCommand(); // 创建 command 对象

            string strOffice = Request["Office"].ToString().Trim();
            string strCounsellor_Writer;
            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            string sSql = "";

            sSql = "select Student_Name, First_Payment + Second_Payment as 'Yajing', 50  as 'Bonus' from contract where ";

            string strFilter = "";
            /// <newpara>
            /// A) 文案零中介完成Offer奖， 收到第一个offer，First_Payment >=1000
            /// </newpara>
            //strFilter += "Office like '" + strOffice + "' AND";
            strFilter += "( Date_Completion_Offer >= '" + Common_SXD.strStartDate + "' AND  Date_Completion_Offer <= '" + Common_SXD.strEndingDate + "' ) AND (First_Payment + Second_Payment) >= 1000  AND Student_Type = '零中介'  AND Writer = '" + strCounsellor_Writer + "'";

            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);

            sqlCmd.CommandText = sSql + strFilter;

            SqlDataReader sqlReader;
            try
            {
                sqlReader = sqlCmd.ExecuteReader();
            }
            catch (Exception err)
            {
                Response.Write("<li>打开数据库出错：" + err.ToString());
                sqlCnn.Close(); // 关闭数据库连接
                return;
            }


            // 读入第一个查询结果集， 即 Contract 中的数据
            int nCol = sqlReader.FieldCount;

            //首先生成标题行
            {
                HtmlTableRow row = new HtmlTableRow();
                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    string temp = Common_SXD.English_Chinese(sqlReader.GetName(i));
                    cell.Controls.Add(new LiteralControl(temp.Trim()));
                    row.Cells.Add(cell);
                }

                Table_Bonus_7.Rows.Add(row);
                row.BgColor = "Tan";
            }

            // 依次读出数据
            //			int j=0; 
            while (sqlReader.Read())
            {
                HtmlTableRow row = new HtmlTableRow();
                String sNo_Series = sqlReader.GetString(0);
                // Provide a different background color for alternating rows.
                //				if (j%2 == 1)
                //					row.BgColor="Gainsboro";


                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
                    row.Cells.Add(cell);
                    if (i == nCol - 1)
                        J7 += Convert.ToDecimal(sqlReader.GetValue(i).ToString());
                }

                Table_Bonus_7.Rows.Add(row);
                //				j++;
            }
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接

            ////////////////生成小计
            {
                HtmlTableRow row = new HtmlTableRow();

                HtmlTableCell cell = new HtmlTableCell();
                cell.Controls.Add(new LiteralControl(" "));
                row.Cells.Add(cell);

                HtmlTableCell cell2 = new HtmlTableCell();
                cell2.Controls.Add(new LiteralControl("合计:"));
                row.Cells.Add(cell2);

                HtmlTableCell cell3 = new HtmlTableCell();
                cell3.Controls.Add(new LiteralControl(J7.ToString().Trim()));
                row.Cells.Add(cell3);

                Table_Bonus_7.Rows.Add(row);
                //			row.BgColor="Tan";
            }
            ///////////////////////////////

        }

        /// <summary>
        /// 顾问、文案控件的初始化
        /// </summary>
        private void Init_CounsellorWriter()
        {

            string sDDList_Office = Request["Office"];

            SqlConnection sqlCnn = new SqlConnection(strCnn);

            // 载入顾问、文案数据，绑定到 DDList_Counsellor_Writer 中
            string sSql = "select * from Employees ";
            sSql += "where ( Is_Counsellor = 1 or  Is_Writer = 1 ) and Office ='" + sDDList_Office + "'";
            sSql += "order by Code_Employee asc";
            SqlDataAdapter sqlDA = new SqlDataAdapter(sSql, sqlCnn);
            sqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            DataSet bsDataSet = new DataSet();
            sqlDA.Fill(bsDataSet, "Employees");
            DDList_Counsellor_Writer.DataSource = bsDataSet.Tables["Employees"];
            DDList_Counsellor_Writer.DataBind();
            sqlCnn.Close();

        }

        /// <summary>
        /// 地区、计奖年、月的初始化
        /// </summary>
        private void Init_Office_YearMonth()
        {
             TextBox_Year_Month.Text = Common_SXD.TimeToYM(DateTime.Now.ToString());
        }


        public class SynchronousSocketClient
        {

            public bool StartClient(string strOffice, string strCounsellor_Writer, string tempYear, string tempMonth, HttpResponse myResponse)
            {
                // Data buffer for incoming data.
                byte[] bytes = new byte[1024];

                // Connect to a remote device.
                try
                {
                    // Establish the remote endpoint for the socket.
                    // The name of the
                    // remote device is "host.contoso.com".
                    //				IPHostEntry ipHostInfo = Dns.Resolve("localhost");
                    IPHostEntry ipHostInfo = Dns.GetHostEntry("localhost");
                    IPAddress ipAddress = ipHostInfo.AddressList[0];
                    IPEndPoint remoteEP = new IPEndPoint(ipAddress, 10086);

                    // Create a TCP/IP  socket.
                    Socket sender = new Socket(AddressFamily.InterNetwork,
                        SocketType.Stream, ProtocolType.Tcp);

                    // Connect the socket to the remote endpoint. Catch any errors.
                    try
                    {
                        sender.Connect(remoteEP);

                        Console.WriteLine("Socket connected to {0}",
                            sender.RemoteEndPoint.ToString());

                        // Encode the data string into a byte array.
                        byte[] msg = Encoding.Default.GetBytes("MakeBonus3In1 " + strOffice + " " + strCounsellor_Writer + " " + tempYear + " " + tempMonth + "  \r\n");

                        // Send the data through the socket.
                        int bytesSent = sender.Send(msg);

                        // Receive the response from the remote device.
                        int bytesRec = sender.Receive(bytes);
                        Console.WriteLine("Echoed test = {0}",
                            Encoding.ASCII.GetString(bytes, 0, bytesRec));

                        // Release the socket.
                        sender.Shutdown(SocketShutdown.Both);
                        sender.Close();

                    }
                    catch (ArgumentNullException ane)
                    {
                        Console.WriteLine("ArgumentNullException : {0}", ane.ToString());
                        return false;
                    }
                    catch (SocketException se)
                    {
                        Console.WriteLine("SocketException : {0}", se.ToString());
                        return false;
                    }
                    catch (Exception e)
                    {
                        Console.WriteLine("Unexpected exception : {0}", e.ToString());
                        return false;
                    }

                }
                catch (Exception e)
                {
                    Console.WriteLine(e.ToString());
                    return false;
                }
                return true;
            }

        }

        /// <summary>
        /// 发送奖金表到您的邮箱
        /// </summary>
        private void Sendmail_Bonus_Exel()
        {
            /// <newpara> 
            /// 读取地区、顾问（文案）字段
            /// </newpara>
            Common_SXD.Set_BonusDate(TextBox_Year_Month.Text, false);

            string strOffice = Request["Office"].ToString().Trim();
            string strCounsellor_Writer;
            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            SynchronousSocketClient MyClient = new SynchronousSocketClient();

            MyClient.StartClient(strOffice, strCounsellor_Writer, Common_SXD.tempYear, Common_SXD.tempMonth, Response); //			StartClient();

            Thread.Sleep(5000);

            try
            {
                // 服务器名，端口号
                System.Net.Mail.SmtpClient smtp = new System.Net.Mail.SmtpClient(Common_SXD.smtp_ukeas, 25);
                smtp.UseDefaultCredentials = false;
                //账户，密码
                smtp.Credentials = new System.Net.NetworkCredential("jianwei_rong@promisingEDU.com", Common_SXD.pwd_jianwei_rong);
                smtp.DeliveryMethod = SmtpDeliveryMethod.Network;

                System.Net.Mail.MailMessage myMsg = new System.Net.Mail.MailMessage();
                //发件人
                myMsg.From = new MailAddress("jianwei_rong@promisingEDU.com", "戎建伟", Encoding.GetEncoding("GB2312"));

                myMsg.SubjectEncoding = Encoding.GetEncoding("GB2312");
                myMsg.BodyEncoding = Encoding.GetEncoding("GB2312");
                myMsg.Subject = strOffice + " " + strCounsellor_Writer + "奖金表";
                myMsg.Body = "本邮件是数据库的奖金计算结果，请不要回复邮件。";


                string strEmail = Session["Email"].ToString().Trim();
                string sAddress_Email = Common_SXD.eMail_Replacement(strEmail); ///strEmail + "@promisingEDU.com";
                //收件人
                myMsg.To.Add(new MailAddress(sAddress_Email, "", Encoding.GetEncoding("GB2312")));
                //密送
                ///myMsg.Bcc.Add(new MailAddress("rongjianwei@sina.com", "", Encoding.GetEncoding("GB2312")));

                Response.Charset = "gb2312";

                //读取当前目录的path
                //				string strFilePath = Directory.GetCurrentDirectory();
                //读取服务器物理目录
                string strFilePath = Request.PhysicalApplicationPath;
                Common_SXD.Set_BonusDate(TextBox_Year_Month.Text, false);
                string file = strFilePath + @"..\..\Model_Bonus\" + strOffice + "_" +
                    strCounsellor_Writer + Common_SXD.tempDate + "(3合1)奖金申报表" + ".xls";

                //添加附件
                System.Net.Mail.Attachment attachment = new System.Net.Mail.Attachment(file);
                myMsg.Attachments.Add(attachment);

                myMsg.Priority = System.Net.Mail.MailPriority.Low;

                smtp.Send(myMsg);

                Response.Clear();
                Response.Write("<li>奖金表已经发送！</li>");
 //               MessageBox MyB = new MessageBox(this);
 //               MyB.Show("奖金表已经发送！");

            }
            catch (System.Net.Mail.SmtpException se)
            {
                Response.Write("<li>smtp error.</li>" + se.Message);
            }
            catch (FormatException se)
            {
                Response.Write("<li>format error.</li>" + se.Message);
            }
            catch (System.Exception e)
            {
                Response.Write("<li>system error.</li>" + e.Message + "; Target: " + e.TargetSite);
            }

        }

        /// <summary>
        /// 顾问、文案的中文名+地区，转换成电子邮件信箱
        /// 未完成的函数
        /// </summary>
        public string ChineseNameToEmail(string sOffice, string sC_N_Employee)
        {
            return "jianwei_rong" + "@promisingEDU.com";
        }

        protected void Bt_Refress_Search_Click(object sender, EventArgs e)
        {
            // 填写奖金计算网页
            Common_SXD.Set_BonusDate(TextBox_Year_Month.Text,false);
            Init_Tables_Bonus_1();
            Init_Tables_Bonus_2();

            Init_Tables_Bonus_3();
            Init_Tables_Bonus_4();

            Init_Tables_Bonus_5();
            Init_Tables_Bonus_6();

            Init_Tables_Bonus_7();
            Init_Tables_Bonus_8();

            Init_Tables_Bonus_9();
            Init_Tables_Bonus_10();

            int tMonth = DateTime.Now.Month;
            int tYear = DateTime.Now.Year;
            Init_Total_Bonus(TextBox_Year_Month.Text);

            System.DateTime bonusDateTime20091001 =
                new System.DateTime(2009,        // Year
                10,            // Month
                1,            // Day
                0,            // Hour
                0,            // Minute
                0,            // Second
                0             // Millisecond
                );
/*
            string temp = TextBox_Year_Month.Text + "-1";
            System.DateTime bonusNow = Convert.ToDateTime(temp);
            if ( bonusNow < bonusDateTime20091001)
                Bt_Bonus_Exel.Enabled = true; //允许发送 excel格式的3合1奖金表
            else
                Bt_Bonus_Exel.Enabled = false; 
*/

        }

        protected void Bt_Bonus_Exel_Click(object sender, EventArgs e)
        {
            Sendmail_Bonus_Exel();
        }


    }
}
